﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace mamnontuoitho7.AppCode
{
    public class Conversation
    {
         public ConnectGetData _connectData;
         public Conversation()
        {
            _connectData = new ConnectGetData();
         }
         public void ConversationCreate(String classID, String topic, String ownerID)
         {
             SqlConnection con = _connectData.GetConnect();
             
             try
             {
                 string sql = "insert [Conversation] values (@ClassID, @Topic, default, default, @OwnerID, 1,0)";
                 SqlCommand cmd = new SqlCommand(sql, con);
                 cmd.Parameters.AddWithValue("@ClassID", classID);
                 cmd.Parameters.AddWithValue("@Topic", topic);
                 cmd.Parameters.AddWithValue("@OwnerID", ownerID);
                 con.Open();
                 cmd.ExecuteNonQuery();
                 cmd.Dispose();
                 con.Close();
             }
             catch (Exception ex)
             {
                 System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);

             }
         }

         /// <summary>
         /// get a number of record of a page for parent
         /// </summary>
         /// <param name="pageIndex"></param>
         /// <param name="step"></param>
         /// <returns>datatable</returns>
         public DataTable GetConversationPaged(int pageIndex, int step, String memberID)
         {
             //int startRowIndex = pageIndex * step + 1;
             //int endRowIndex = startRowIndex + step -1;
             int startRowIndex = (pageIndex - 1) * step + 1;
             int endRowIndex = startRowIndex + step - 1;
             string sql = "SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY ConversationID DESC) AS number, * FROM ConversationView where ConversationVisable = 1 and OwnerID =" + memberID + " ) AS TempTable WHERE number BETWEEN @startRowIndex AND @endRowIndex";
             SqlConnection con = _connectData.GetConnect();
             //      con.Open();
             SqlCommand cmd = new SqlCommand(sql, con);

             // cmd.Parameters.AddWithValue("@TableName", tableName);
             cmd.Parameters.AddWithValue("@startRowIndex", startRowIndex);
             cmd.Parameters.AddWithValue("@endRowIndex", endRowIndex);
             DataTable dt = _connectData.GetDataTable(cmd);
             //       con.Close();
             return dt;
         }

         /// <summary>
         /// get total number of conversation of one member:parent
         /// </summary>
         /// <param name="articleTypeID"></param>
         /// <returns>int</returns>
         public global::System.Nullable<int> TotalConversationOfOneMember(String memberID)
         {

             SqlConnection con = _connectData.GetConnect();

             con.Open();

             string sql = "SELECT COUNT(*)FROM [Conversation] WHERE ConversationVisable =1 and OwnerID = "+memberID;
             SqlCommand cmd = new SqlCommand(sql, con);

             object returnValue;

             returnValue = cmd.ExecuteScalar();
             con.Close();
             if (((returnValue == null) || (returnValue.GetType() == typeof(global::System.DBNull))))
             {
                 return new global::System.Nullable<int>();
             }
             else
             {
                 return new global::System.Nullable<int>(((int)(returnValue)));
             }
         }

         /// <summary>
         /// get conversatoin by id for user, 
         /// </summary>
         /// <param name="productID"></param>
         /// <returns></returns>
         public DataTable GetConversationByID(String conversationID)
         {
             SqlConnection con = _connectData.GetConnect();
             string sql = "select * from ConversationView where ConversationID = @ConversationID and ConversationVisable =1";
             SqlCommand cmd = new SqlCommand(sql, con);
             cmd.Parameters.AddWithValue("@ConversationID", conversationID);
             DataTable dt = _connectData.GetDataTable(cmd);
             return dt;
         }


         /// <summary>
         /// get conversatoin by class for user<teacher>, 
         /// </summary>
         /// <param name="productID"></param>
         /// <returns></returns>
         public DataTable GetConversationByClass(String ClassID)
         {
             SqlConnection con = _connectData.GetConnect();
             string sql = "select * from ConversationView where ClassID = @ClassID and ConversationVisable =1";
             SqlCommand cmd = new SqlCommand(sql, con);
             cmd.Parameters.AddWithValue("@ClassID", ClassID);
             DataTable dt = _connectData.GetDataTable(cmd);
             return dt;
         }

         /// <summary>
         /// get total number of conversation of one class
         /// </summary>
         /// <param name="articleTypeID"></param>
         /// <returns>int</returns>
         public global::System.Nullable<int> TotalConversationOfOneClass(String classID)
         {

             SqlConnection con = _connectData.GetConnect();

             con.Open();

             string sql = "SELECT COUNT(*)FROM [Conversation] WHERE ConversationVisable =1 and ClassID = " + classID;
             SqlCommand cmd = new SqlCommand(sql, con);

             object returnValue;

             returnValue = cmd.ExecuteScalar();
             con.Close();
             if (((returnValue == null) || (returnValue.GetType() == typeof(global::System.DBNull))))
             {
                 return new global::System.Nullable<int>();
             }
             else
             {
                 return new global::System.Nullable<int>(((int)(returnValue)));
             }
         }

         /// <summary>
         /// get a number of record of a page for class:teacher
         /// </summary>
         /// <param name="pageIndex"></param>
         /// <param name="step"></param>
         /// <returns>datatable</returns>
         public DataTable GetConversationClassPaged(int pageIndex, int step, String classID)
         {
             //int startRowIndex = pageIndex * step + 1;
             //int endRowIndex = startRowIndex + step -1;
             int startRowIndex = (pageIndex - 1) * step + 1;
             int endRowIndex = startRowIndex + step - 1;
             string sql = "SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY ConversationID DESC) AS number, * FROM ConversationView where ConversationVisable = 1 and ClassID =" + classID + " ) AS TempTable WHERE number BETWEEN @startRowIndex AND @endRowIndex";
             SqlConnection con = _connectData.GetConnect();
             //      con.Open();
             SqlCommand cmd = new SqlCommand(sql, con);

             // cmd.Parameters.AddWithValue("@TableName", tableName);
             cmd.Parameters.AddWithValue("@startRowIndex", startRowIndex);
             cmd.Parameters.AddWithValue("@endRowIndex", endRowIndex);
             DataTable dt = _connectData.GetDataTable(cmd);
             //       con.Close();
             return dt;
         }


         /// <summary>
         /// count unanswered conversation for parent
         /// </summary>
         /// <param name="articleTypeID"></param>
         /// <returns>int</returns>
         public global::System.Nullable<int> TotalUnanswerConversationOfOneParent(String ownerID)
         {

             SqlConnection con = _connectData.GetConnect();

             con.Open();

             string sql = "select  COUNT(ConversationID) from  ConversationView  where  OwnerID = " + ownerID + " and IsReadOwner = 0";
             SqlCommand cmd = new SqlCommand(sql, con);

             object returnValue;

             returnValue = cmd.ExecuteScalar();
             con.Close();
             if (((returnValue == null) || (returnValue.GetType() == typeof(global::System.DBNull))))
             {
                 return new global::System.Nullable<int>();
             }
             else
             {
                 return new global::System.Nullable<int>(((int)(returnValue)));
             }
         }

         /// <summary>
         /// count unanswered conversation for class
         /// </summary>
         /// <param name="articleTypeID"></param>
         /// <returns>int</returns>
         public global::System.Nullable<int> TotalUnanswerConversationOfOneClass(String classID)
         {

             SqlConnection con = _connectData.GetConnect();

             con.Open();

             string sql = "select  COUNT(ConversationID) from  ConversationView  where  ClassID = " + classID + " and IsReadClass = 0";
             SqlCommand cmd = new SqlCommand(sql, con);

             object returnValue;

             returnValue = cmd.ExecuteScalar();
             con.Close();
             if (((returnValue == null) || (returnValue.GetType() == typeof(global::System.DBNull))))
             {
                 return new global::System.Nullable<int>();
             }
             else
             {
                 return new global::System.Nullable<int>(((int)(returnValue)));
             }
         }

         /// <summary>
         /// count 3-day notice for parent
         /// </summary>
         /// <param name="number"></param>
         /// <returns>int</returns>
         public global::System.Nullable<int> Count3DayNotice(String number, String classID)
         {
             
             SqlConnection con = _connectData.GetConnect();

             con.Open();

             string sql = "select  COUNT(NoticeID) from  Notice  where DATEDIFF(DD, CreateDate, GETDATE()) < " + number + " and ClassID in (" + classID + ")";
             SqlCommand cmd = new SqlCommand(sql, con);

             object returnValue;

             returnValue = cmd.ExecuteScalar();
             con.Close();
             if (((returnValue == null) || (returnValue.GetType() == typeof(global::System.DBNull))))
             {
                 return new global::System.Nullable<int>();
             }
             else
             {
                 return new global::System.Nullable<int>(((int)(returnValue)));
             }
         }

        /// <summary>
        /// update status of a conversation parent.
        /// </summary>
        /// <param name="conversationID"></param>
        /// <param name="isReadOwner"></param>
        /// <param name="isReadClass"></param>
         public void ChangeStatusOwner(String conversationID, String isReadOwner)
         {
             SqlConnection con = _connectData.GetConnect();

             try
             {
                 con.Open();
                 string sql2 = "update [Conversation] set IsReadOwner = @IsReadOwner where ConversationID = @ConversationID";
                 SqlCommand cmd2 = new SqlCommand(sql2, con);
                 cmd2.Parameters.AddWithValue("@IsReadOwner", isReadOwner);

                 cmd2.Parameters.AddWithValue("@ConversationID", conversationID);
                 cmd2.ExecuteNonQuery();
                 cmd2.Dispose();

                 con.Close();
             }
             catch (Exception ex)
             {
                 System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);

             }
         }


         /// <summary>
         /// update status of a conversation class.
         /// </summary>
         /// <param name="conversationID"></param>
         /// <param name="isReadOwner"></param>
         /// <param name="isReadClass"></param>
         public void ChangeStatusClass(String conversationID, String isReadClass)
         {
             SqlConnection con = _connectData.GetConnect();

             try
             {
                 con.Open();
                 string sql2 = "update [Conversation] set IsReadClass = @IsReadClass where ConversationID = @ConversationID";
                 SqlCommand cmd2 = new SqlCommand(sql2, con);
                 cmd2.Parameters.AddWithValue("@IsReadClass", isReadClass);

                 cmd2.Parameters.AddWithValue("@ConversationID", conversationID);
                 cmd2.ExecuteNonQuery();
                 cmd2.Dispose();

                 con.Close();
             }
             catch (Exception ex)
             {
                 System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);

             }
         }

         /// <summary>
         /// check if the conversation belong to the current username : parent
         /// </summary>
         /// <param name="username"></param>
         /// <param name="commentID"></param>
         /// <returns>true if the current user is the owner</returns>
         public bool checkConversationOwnerForParent(String parentID, String conversationID)
         {
             bool flag = false;

             try
             {
                 string sql = "select * from ConversationView where OwnerID = @OwnerID and ConversationID =@ConversationID ";

                 SqlCommand cmd = new SqlCommand(sql, _connectData.GetConnect());
                 cmd.Parameters.AddWithValue("@OwnerID", parentID);
                 cmd.Parameters.AddWithValue("@ConversationID", conversationID);
                 DataTable dt = _connectData.GetDataTable(cmd);

                 if (dt.Rows.Count > 0)
                 {
                     flag = true;
                 }
                 cmd.Dispose();
             }
             catch (Exception ex)
             {
                 System.Console.WriteLine("Error error error: " + ex.StackTrace);
                 return flag;
             }
             return flag;
         }

         /// <summary>
         /// check if the conversation belong to the current username : teacher
         /// </summary>
         /// <param name="username"></param>
         /// <param name="commentID"></param>
         /// <returns>true if the current user is the owner</returns>
         public bool checkConversationOwnerForTeacher(String classID, String conversationID)
         {
             bool flag = false;

             try
             {
                 string sql = "select * from ConversationView where ClassID = @ClassID and ConversationID =@ConversationID ";

                 SqlCommand cmd = new SqlCommand(sql, _connectData.GetConnect());
                 cmd.Parameters.AddWithValue("@ClassID", classID);
                 cmd.Parameters.AddWithValue("@ConversationID", conversationID);
                 DataTable dt = _connectData.GetDataTable(cmd);

                 if (dt.Rows.Count > 0)
                 {
                     flag = true;
                 }
                 cmd.Dispose();
             }
             catch (Exception ex)
             {
                 System.Console.WriteLine("Error error error: " + ex.StackTrace);
                 return flag;
             }
             return flag;
         }
    }
}